﻿Imports System.Data.SqlClient

Public Class FrmTimKiemTenChu
    Private Sub NapDonViHuyen()
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand(" select * from TENHUYEN", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        CmbHuyen.DisplayMember = "T_huyen"
        CmbHuyen.ValueMember = "maH"
        CmbHuyen.DataSource = tbl
        ob_cnn.Close()
    End Sub
    Private Sub NapLoaiDat2()
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand(" select kyhieu, tenld from LOAIDAT", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        CmbLD2.DisplayMember = "kyhieu"
        CmbLD2.ValueMember = "kyhieu"
        CmbLD2.DataSource = tbl
        ob_cnn.Close()
    End Sub
    Private Sub NapLoaiDat1()
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand(" select kyhieu, tenld from LOAIDAT", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        CmbLD1.DisplayMember = "kyhieu"
        CmbLD1.ValueMember = "kyhieu"
        CmbLD1.DataSource = tbl
        ob_cnn.Close()
    End Sub
    Private Sub NapLoaiBienDong()
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand(" select * from LOAIBD", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        CmbLoaiBD.DisplayMember = "TenLoaiBD"
        CmbLoaiBD.ValueMember = "maBD"
        CmbLoaiBD.DataSource = tbl
        ob_cnn.Close()
    End Sub
    Private Sub NapPhong()
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand(" select * from DMPhong", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        cmbPhong.DisplayMember = "Tenphong"
        cmbPhong.ValueMember = "maPhongID"
        cmbPhong.DataSource = tbl
        ob_cnn.Close()
    End Sub
    Private Sub NapNgan()
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand(" select * from DMNgan", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        cmbNgan.DisplayMember = "TenNgan"
        CmbNgan.ValueMember = "maNganID"
        CmbNgan.DataSource = tbl
        ob_cnn.Close()
    End Sub
    Private Sub NapKe()
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand(" select * from DMKe", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        CmbKe.DisplayMember = "TenKe"
        CmbKe.ValueMember = "maKeID"
        CmbKe.DataSource = tbl
        ob_cnn.Close()
    End Sub
    Private Sub NapKho()
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand(" select * from DMKho", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        CmbKho.DisplayMember = "TenKho"
        CmbKho.ValueMember = "maKhoID"
        CmbKho.DataSource = tbl
        ob_cnn.Close()
    End Sub
    Private Sub NapTinhTrang()
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand(" select * from DMTinhTrang", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        CmbTinhTrang.DisplayMember = "TenTinhTrang"
        CmbTinhTrang.ValueMember = "maTinhTrangID"
        CmbTinhTrang.DataSource = tbl
        ob_cnn.Close()
    End Sub
    Private Sub CmbHuyen_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CmbHuyen.SelectedIndexChanged
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand(" select * from TenXa where mahuyen = '" & CmbHuyen.SelectedValue.ToString & "' ", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        CmbXa.DisplayMember = "TenXa"
        CmbXa.ValueMember = "maxa"
        CmbXa.DataSource = tbl
        ob_cnn.Close()
    End Sub
    Private Sub LoadTen()
        ChuSD1.DataBindings.Clear()
        ChuSD1.DataBindings.Add("Text", DataGridViewTen.DataSource, "chusd1")
        NapDataGridViewtenchu()
    End Sub

    Private Sub FrmTimThongTin_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        NapDonViHuyen()
        NapLoaiDat1()
        NapLoaiDat2()
        NapLoaiBienDong()
        Tat_chuc_nang()
        NapPhong()
        NapNgan()
        NapKe()
        NapKho()
        NapTinhTrang()
        DataGridViewBD.RowsDefaultCellStyle.BackColor = Color.Beige
        DataGridViewBD.AlternatingRowsDefaultCellStyle.BackColor = Color.Azure
        DataGridViewTen.RowsDefaultCellStyle.BackColor = Color.Beige
        DataGridViewTen.AlternatingRowsDefaultCellStyle.BackColor = Color.Azure
    End Sub
    Private Sub Tat_chuc_nang()
        Me.ChuSD1.Enabled = True
        Me.TextDiaChiTruoc.Enabled = False
        Me.TextToBD1.Enabled = False
        Me.TextSoThua1.Enabled = False
        Me.TextDienTich1.Enabled = False
        Me.CmbLD1.Enabled = False
        Me.TextSoThua2.Enabled = False
        Me.TextDienTich2.Enabled = False
        Me.CmbLD2.Enabled = False
        Me.TextNoiDungBD.Enabled = False
        Me.DataGridViewBD.Enabled = True
        Me.DateNgayBD.Enabled = False
        Me.CmbLoaiBD.Enabled = False
    End Sub
    Private Sub Bat_chuc_nang()
        Me.ChuSD1.Enabled = True
        Me.TextDiaChiTruoc.Enabled = True
        Me.TextToBD1.Enabled = True
        Me.TextSoThua1.Enabled = True
        Me.TextDienTich1.Enabled = True
        Me.CmbLD1.Enabled = True
        Me.TextSoThua2.Enabled = True
        Me.TextDienTich2.Enabled = True
        Me.CmbLD2.Enabled = True
        Me.TextNoiDungBD.Enabled = True
        Me.DataGridViewBD.Enabled = True
        Me.DateNgayBD.Enabled = True
        Me.CmbLoaiBD.Enabled = True
    End Sub
    Private Sub NapDataGridViewtenchu()
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand("select maBDid, bd.ngayBD, bd.ChuSD1,bd.cho_o1," &
                                " bd.toBD, bd.sothua1,bd.dientich1, bd.loaidat1id, bd.NoiDungBD," &
                                "  bd.sothua2,bd.dientich2," &
                                " bd.loaidat2id, lbd.tenloaibd , p.tenphong, ng.tenngan, k.tenke, kh.tenkho, " &
                                " tt.TenTinhTrang From DMTinhTrang tt, DMPhong p, DMNgan ng, DMKe k, DMKho kh,loaiBD lbd, " &
                                " BIENDONG bd, tenxa tx, tenhuyen th WHERE bd.loaibdid = lbd.mabd " &
                                " and bd.diachixaid = tx.maxa and bd.diachihuyenid= th.mah and bd.maphongID = p.maphongid " &
                                " and bd.manganid = ng.manganid and bd.makeid = k.makeid and bd.makhoid = kh.makhoid" &
                                " and bd.tinhTrangid= tt.matinhtrangid and bd.chusd1  = N'" & ChuSD1.Text & "'", ob_cnn) '
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        DataGridViewBD.DataSource = tbl
        ob_cnn.Close()
    End Sub
    Private Sub NapDataGridViewten()
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand("select  ChuSD1  From  BIENDONG  WHERE chusd1  like N'%" & ChuSD1.Text & "%'" &
                                " and diachixaid = '" & CmbXa.SelectedValue.ToString & "' " &
                                " and diachihuyenid = '" & CmbHuyen.SelectedValue.ToString & "'group by chusd1 ", ob_cnn) '
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        DataGridViewTen.DataSource = tbl
        ob_cnn.Close()
    End Sub
    Private Sub Buttonlamtuoi_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Buttonlamtuoi.Click
        ChuSD1.Text = ""
        NapDataGridViewten()
    End Sub
    Private Sub Form1_FormClosing(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing
        'If MsgBox("Bạn muốn thoát khỏi chương trình không ?", MsgBoxStyle.Question + MsgBoxStyle.YesNo, "Thoát chương trình") = MsgBoxResult.No Then
        '    e.Cancel = True
        'End If
    End Sub

    Private Sub ButtonThoat_Click(sender As Object, e As EventArgs) Handles ButtonThoat.Click
        Me.Close()

    End Sub
    Private Sub ChuSD1_TextChanged(sender As Object, e As EventArgs) Handles ChuSD1.TextChanged
        NapDataGridViewten()
    End Sub

    Private Sub DataGridViewTen_CellContentClick(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridViewTen.CellContentClick
        LoadTen()
        NapDataGridViewtenchu()
    End Sub

    Private Sub GroupBox1_Enter(sender As Object, e As EventArgs) Handles GroupBox1.Enter

    End Sub

    Private Sub DataGridViewBD_CellContentClick(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridViewBD.CellContentClick
        ChuSD1.DataBindings.Clear()
        ChuSD1.DataBindings.Add("Text", DataGridViewBD.DataSource, "chusd1")
        DateNgayBD.DataBindings.Clear()
        DateNgayBD.DataBindings.Add("Text", DataGridViewBD.DataSource, "NgayBD")
        TextToBD1.DataBindings.Clear()
        TextToBD1.DataBindings.Add("Text", DataGridViewBD.DataSource, "tobd")
        TextSoThua1.DataBindings.Clear()
        TextSoThua1.DataBindings.Add("Text", DataGridViewBD.DataSource, "sothua1")
        TextDienTich1.DataBindings.Clear()
        TextDienTich1.DataBindings.Add("Text", DataGridViewBD.DataSource, "dientich1")
        TextNoiDungBD.DataBindings.Clear()
        TextNoiDungBD.DataBindings.Add("Text", DataGridViewBD.DataSource, "noidungbd")
        TextDiaChiTruoc.DataBindings.Clear()
        TextDiaChiTruoc.DataBindings.Add("Text", DataGridViewBD.DataSource, "cho_o1")
        CmbLD1.DataBindings.Clear()
        CmbLD1.DataBindings.Add("Text", DataGridViewBD.DataSource, "loaidat1id")
        CmbLD2.DataBindings.Clear()
        CmbLD2.DataBindings.Add("Text", DataGridViewBD.DataSource, "loaidat2id")
        TextSoThua2.DataBindings.Clear()
        TextSoThua2.DataBindings.Add("Text", DataGridViewBD.DataSource, "sothua2")
        TextDienTich2.DataBindings.Clear()
        TextDienTich2.DataBindings.Add("Text", DataGridViewBD.DataSource, "dientich2")
        CmbLoaiBD.DataBindings.Clear()
        CmbLoaiBD.DataBindings.Add("Text", DataGridViewBD.DataSource, "tenloaibd")
       
        '------
        cmbPhong.DataBindings.Clear()
        cmbPhong.DataBindings.Add("Text", DataGridViewBD.DataSource, "tenphong")
        CmbNgan.DataBindings.Clear()
        CmbNgan.DataBindings.Add("Text", DataGridViewBD.DataSource, "tenngan")
        CmbKe.DataBindings.Clear()
        CmbKe.DataBindings.Add("Text", DataGridViewBD.DataSource, "tenke")
        CmbKho.DataBindings.Clear()
        CmbKho.DataBindings.Add("Text", DataGridViewBD.DataSource, "tenkho")
        CmbTinhTrang.DataBindings.Clear()
        CmbTinhTrang.DataBindings.Add("Text", DataGridViewBD.DataSource, "tentinhtrang")
    End Sub

    Private Sub GroupBox2_Enter(sender As Object, e As EventArgs) Handles GroupBox2.Enter

    End Sub

    Private Sub DataGridViewTen_CellFormatting(sender As Object, e As DataGridViewCellFormattingEventArgs) Handles DataGridViewTen.CellFormatting
        '   DataGridViewTen.Rows(e.RowIndex).HeaderCell.Value = CStr(e.RowIndex + 1)
    End Sub

    Private Sub DataGridViewBD_CellFormatting(sender As Object, e As DataGridViewCellFormattingEventArgs) Handles DataGridViewBD.CellFormatting

    End Sub

    Private Sub DataGridViewTen_RowPostPaint(sender As Object, e As DataGridViewRowPostPaintEventArgs) Handles DataGridViewTen.RowPostPaint
        Dim strRowNumber As String = (e.RowIndex + 1).ToString
        While (strRowNumber.Length < DataGridViewTen.RowCount.ToString.Length)
            strRowNumber = "0" & strRowNumber
        End While
        Dim Size As SizeF = e.Graphics.MeasureString(strRowNumber, MyBase.Font)
        If DataGridViewTen.RowHeadersWidth < Size.Width + 20 Then DataGridViewTen.RowHeadersWidth = Size.Width + 20
        Dim b As Brush = SystemBrushes.ControlText
        e.Graphics.DrawString(strRowNumber, MyBase.Font, b, e.RowBounds.Location.X + 15, e.RowBounds.Location.Y +
                              ((e.RowBounds.Height - Size.Height) / 2))
    End Sub

    Private Sub DataGridViewBD_RowPostPaint(sender As Object, e As DataGridViewRowPostPaintEventArgs) Handles DataGridViewBD.RowPostPaint
        Dim strRowNumber As String = (e.RowIndex + 1).ToString
        While (strRowNumber.Length < DataGridViewBD.RowCount.ToString.Length)
            strRowNumber = "0" & strRowNumber
        End While
        Dim Size As SizeF = e.Graphics.MeasureString(strRowNumber, MyBase.Font)
        If DataGridViewBD.RowHeadersWidth < Size.Width + 20 Then DataGridViewBD.RowHeadersWidth = Size.Width + 20
        Dim b As Brush = SystemBrushes.ControlText
        e.Graphics.DrawString(strRowNumber, MyBase.Font, b, e.RowBounds.Location.X + 15, e.RowBounds.Location.Y +
                              ((e.RowBounds.Height - Size.Height) / 2))
    End Sub

    Private Sub ButtonXuatExcel_Click(sender As Object, e As EventArgs) Handles ButtonXuatExcel.Click
        XuatExcel()
    End Sub
    Private Sub XuatExcel()
        ' xuat truc tiep tu datagridview
        Dim gr As SaveFileDialog = New SaveFileDialog()
        Dim FileName As String
        With gr
            .InitialDirectory = "D:\"
            .Filter = "Microsoft Excel Worksheet (*.xls)|*.xls"
            .FilterIndex = 1
        End With
        If gr.ShowDialog = System.Windows.Forms.DialogResult.OK Then
            FileName = gr.FileName
        Else
            Exit Sub
        End If
        'Extracting from database
        Dim col, row As Integer
        Dim Excel As Object = CreateObject("Excel.Application")

        If Excel Is Nothing Then
            MsgBox("m***cel re..", MsgBoxStyle.Critical)
            Return
        End If
        'Export to Excel process
        Try
            With Excel
                .SheetsInNewWorkbook = 1
                .Workbooks.Add()
                .Worksheets(1).Select()
                .Worksheets(1).name = "Thong bao bien dong"
                Dim i As Integer = 1
                For col = 0 To DataGridViewBD.Columns.Count - 1
                    .cells(1, i).value = DataGridViewBD.Columns(col).HeaderText
                    .cells(1, i).EntireRow.Font.Bold = True
                    i += 1
                Next
                i = 2
                Dim k As Integer = 1
                For col = 0 To DataGridViewBD.Columns.Count - 1
                    i = 2
                    For row = 0 To DataGridViewBD.Rows.Count - 1
                        With .worksheets(1)
                            .Cells(i, k).Value = DataGridViewBD.Rows(row).Cells(col).Value
                            i += 1
                        End With
                    Next
                    k += 1
                Next
                .ActiveCell.Worksheet.SaveAs(FileName)
            End With
            System.Runtime.InteropServices.Marshal.ReleaseComObject(Excel)
            Excel = Nothing
            MsgBox("Dữ liệu đã được xuất sang định dạng Excel thành công!", MsgBoxStyle.Information)
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        ' The excel is created and opened for insert value. We most close this excel using this system
        Dim pro() As Process = System.Diagnostics.Process.GetProcessesByName("EXCEL")
        For Each i As Process In pro
            i.Kill()
        Next
    End Sub
End Class
